IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OpenCases]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[OpenCases]
GO

CREATE TABLE [dbo].[OpenCases](
	[CRNumber] [bigint] NOT NULL,
	[PID] [bigint] NOT NULL,
	[Consultant] [bigint] NULL,
	[PatientType] [varchar](1) NOT NULL,
	[RegistrationFees] [smallmoney] NULL,
	[ConsultationFees] [smallmoney] NULL,
	[InitialDeposit] [money] NULL,
	[Package] [bigint] NULL,
	[RegistrationTime] [datetime] NOT NULL
) ON [PRIMARY]


GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.OpenCases TO hms_usr
GO



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_IsPatientCaseOpen') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_IsPatientCaseOpen
GO



/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/03/2008
'SP Name    : dbo.SP_IsPatientCaseOpen
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_IsPatientCaseOpen]
@IDType Varchar(10),
@vchID bigint
AS
IF (@IDType = 'PID')
   BEGIN
   SELECT
       OpenCases.CRNumber AS CRNumber,
	   OpenCases.PatientType AS PatientType,
	   Consultant.[Name] AS Consultant
   FROM OpenCases INNER JOIN Consultant
   ON OpenCases.Consultant = Consultant.ID
   WHERE OpenCases.PID=@vchID
   END
ELSE IF (@IDType = 'CRNumber')
   BEGIN
   SELECT
	   OpenCases.PID AS PID,
       OpenCases.PatientType AS PatientType,
       Consultant.[Name] AS Consultant
   FROM OpenCases INNER JOIN Consultant
   ON OpenCases.Consultant = Consultant.ID
   WHERE OpenCases.CRNumber=@vchID
   END

GRANT EXEC ON dbo.SP_IsPatientCaseOpen TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_UpdateOpenCase') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_UpdateOpenCase
GO


/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/11/2008
'SP Name    : dbo.SP_UpdateOpenCase
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_UpdateOpenCase]

@biCRNumber    bigint,
@vchConsultant    Varchar(50),
@smRegFees smallmoney,
@mDeposit money,
@vchPackage Varchar(50),
@vchPatientType Varchar(1)
                    
AS
DECLARE  @iRetValue integer, 
	     @iConsultantID integer,
		 @iPackageID integer

   IF @vchConsultant IS NOT NULL
       SELECT @iConsultantID = ID FROM Consultant WHERE [Name]=@vchConsultant
   IF @vchPackage IS NOT NULL
	   SELECT @iPackageID = ID FROM Package WHERE [Name]=@vchPackage

   BEGIN
   UPDATE OpenCases
   SET
       Consultant = @iConsultantID,
	   PatientType = @vchPatientType,
	   RegistrationFees = @smRegFees,
	   InitialDeposit = @mDeposit,
	   Package = @iPackageID
   WHERE (CRNumber = @biCRNumber)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg1 varchar(800)
       SET @SPErrMsg1 = 'Error: Error in Stored procedure dbo.SP_UpdateOpenCase for CRNumber : ' + CAST (@biCRNumber as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg1,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

RETURN @iRetValue


GRANT EXEC ON dbo.SP_UpdateOpenCase TO hms_usr
GO
